package Util;

import pojo.classroom;
import pojo.teacher;
import pojo.user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class JDBCUtill {
    public static Boolean Login(String userCount, String passeword) throws Exception {
        //1.注册驱动
        Connection conn = DBConn.getConnection();
        //3.定义sql
        String sql = "select * from db01.user where userCount = ? and password= ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //设置？的值
        pstmt.setString(1, userCount);

        pstmt.setString(2, passeword);

        //执行sql
        ResultSet rs = pstmt.executeQuery();
        if (rs.next()) {
            System.out.println("登陆成功");
            rs.close();
            pstmt.close();
            conn.close();
            return true;
        }
        System.out.println("登陆失败");
        rs.close();
        pstmt.close();
        conn.close();

        return false;
    }

    public static List<classroom> checkDate(String CId) {
        List<classroom> list = new ArrayList<>();
        Connection conn = DBConn.getConnection();
        String sql = "select * from classroom where CId = ?";
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, CId); // 设置查询参数的值
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                int Capacity = rs.getInt("Capacity");
                String Equipment = rs.getString("Equipment");
                String Available = rs.getString("Available");
                String StartTime = rs.getString("StartTime");
                String EndTime = rs.getString("EndTime");
                classroom classroom = new classroom(CId, Capacity, Equipment, Available, StartTime, EndTime);
                list.add(classroom);
            }
            rs.close();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    public static List<user> readUserlDate() {
        List<user> list = new ArrayList<>();
        Connection conn = DBConn.getConnection();
        String sql = "select * from user";
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                String id = rs.getString("id");
                String password = rs.getString("password");
                String userCount = rs.getString("userCount");
                String isAdmin = rs.getString("isAdmin");
                user user=new user(id,password,isAdmin,userCount);
                list.add(user);
            }
            rs.close();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    public static List<teacher> readTeacherlDate() {
        List<teacher> list = new ArrayList<>();
        Connection conn = DBConn.getConnection();
        String sql = "select * from teacher";
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                String TId = rs.getString("TId");
                String TNa = rs.getString("TNa");
                String Title = rs.getString("Title");
                String CNa = rs.getString("CNa");
                String CTm = rs.getString("CTm");
                teacher teacher=new teacher(TId,TNa,Title,CNa,CTm);
                list.add(teacher);
            }
            rs.close();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static List<classroom> readAllDate() {
        List<classroom> list = new ArrayList<>();
        Connection conn = DBConn.getConnection();
        String sql = "select * from classroom";
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                String CId = rs.getString("CId");
                int Capacity = rs.getInt("Capacity");
                String Equipment = rs.getString("Equipment");
                String Available = rs.getString("Available");
                String StartTime = rs.getString("StartTime");
                String EndTime = rs.getString("EndTime");
                classroom classroom = new classroom(CId, Capacity, Equipment, Available, StartTime, EndTime);
                list.add(classroom);
            }
            rs.close();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void addDate(classroom classroom) {
        Connection conn = DBConn.getConnection();
        String sql = "insert into classroom(CId,Capacity,Equipment,Available,StartTime,EndTime) values(?,?,?,?,?,?)";
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, classroom.getCId());
            pstmt.setInt(2, classroom.getCapacity());
            pstmt.setString(3, classroom.getEquipment());
            pstmt.setString(4, classroom.getAvailable());
            pstmt.setString(5, classroom.getStartTime());
            pstmt.setString(6, classroom.getEndTime());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
    public static int isAdm(String userCount) {
        Connection conn = DBConn.getConnection();
        int isAdmin = 0;
        String sql = "SELECT isAdmin FROM db01.user WHERE userCount = ?";
        System.out.println("JDBCUtill.isAdm的isAdmin外面的值是" + isAdmin);
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userCount);
            rs = pstmt.executeQuery();

            if (rs.next()) { // 移动游标到结果集的第一行
                isAdmin = rs.getInt("isAdmin");
                System.out.println("JDBCUtill.isAdm的isAdmin里面的值是" + isAdmin);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return isAdmin;
    }

    public static void delete(String cId) throws SQLException {
        Connection conn = DBConn.getConnection();
        String sql = "delete from classroom where CId = ?";
        PreparedStatement pstmt = null;
        try {
            conn.setAutoCommit(false); // 设置手动提交事务
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, cId);
            pstmt.executeUpdate();

            conn.commit(); // 提交事务
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                System.out.println("发生异常，回滚事务");
                conn.rollback(); // 发生异常时回滚事务
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            conn.setAutoCommit(true); // 恢复自动提交事务
            System.out.println("恢复自动提交事务");
            conn.close();
            System.out.println("关闭连接");
        }
    }

}